﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Dapper;
using AyuBookmark.Common.Interfaces;
using AyuBookmark.Common.Models;
using AyuBookmark.Common.Extensions;

namespace AyuBookmark.Common.Repositories
{
    /// <summary>
    /// 分类数据仓库实现
    /// </summary>
    public class CategoryRepository : BaseRepository<Category, int>, ICategoryRepository
    {
        private const string TableName = "Categories";

        /// <summary>
        /// 初始化分类仓库
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        public CategoryRepository(string connectionString) : base(connectionString, TableName) { }

        #region ICategoryRepository 实现
        /// <inheritdoc />
        public async Task<IEnumerable<Category>> GetTreeStructureAsync()
        {
            using var connection = await GetConnectionAsync();
            var allCategories = await connection.QueryAsync<Category>(
                $"SELECT * FROM {TableName} ORDER BY ParentId, Name");

            return BuildCategoryTree(allCategories);
        }

        /// <inheritdoc />
        public async Task<IEnumerable<Category>> GetPopularCategoriesAsync(int count = 5)
        {
            using var connection = await GetConnectionAsync();
            return await connection.QueryAsync<Category>(
                $@"SELECT * FROM {TableName} 
                   ORDER BY UsageCount DESC, LastUsed DESC
                   LIMIT @count",
                new { count });
        }

        /// <inheritdoc />
        public async Task<bool> UpdateUsageCountAsync(int categoryId)
        {
            using var connection = await GetConnectionAsync();
            var affectedRows = await connection.ExecuteAsync(
                $@"UPDATE {TableName} 
                   SET UsageCount = UsageCount + 1,
                       LastUsed = @now
                   WHERE Id = @categoryId",
                new { categoryId, now = DateTime.Now });

            return affectedRows > 0;
        }
        #endregion

        #region 重写基础方法
        /// <inheritdoc />
        public override async Task<Category> AddAsync(Category category)
        {
            // 设置默认颜色
            if (string.IsNullOrEmpty(category.Color))
            {
                category.Color = GenerateRandomColor();
            }

            return await base.AddAsync(category);
        }

        /// <inheritdoc />
        public override async Task<bool> DeleteAsync(Category category)
        {
            // 检查是否有子分类
            using var connection = await GetConnectionAsync();
            var hasChildren = await connection.ExecuteScalarAsync<bool>(
                $"SELECT EXISTS(SELECT 1 FROM {TableName} WHERE ParentId = @id)",
                new { id = category.Id });

            if (hasChildren)
            {
                throw new InvalidOperationException("Cannot delete category with child categories");
            }

            return await base.DeleteAsync(category);
        }
        #endregion

        #region 私有方法
        private static IEnumerable<Category> BuildCategoryTree(IEnumerable<Category> categories)
        {
            var categoryLookup = categories.ToLookup(c => c.ParentId);

            // 递归构建树形结构
            IEnumerable<Category> BuildTree(int? parentId)
            {
                return categoryLookup[parentId].Select(c => new Category
                {
                    Id = c.Id,
                    Name = c.Name,
                    Color = c.Color,
                    ParentId = c.ParentId,
                    Children = BuildTree(c.Id).ToList()
                });
            }

            return BuildTree(null);
        }

        private static string GenerateRandomColor()
        {
            var random = new Random();
            return $"#{random.Next(0x1000000):X6}";
        }
        #endregion
    }
}